﻿using DBUtil;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using DotNetCommon.Extensions;
using NUnit.Framework;
using Shouldly;
using System.Threading.Tasks;
using System.Data.Common;

namespace Test.SqlServer.Curd.Delete
{
    [TestFixture]
    public class ExecuteTestsAsync : TestBase
    {
        [Test]
        public async Task ExecuteSqlTest()
        {
            //准备表
            if (db.IsTableExist("test"))
            {
                db.Manage.DropTable("test");
            }
            db.ExecuteSql("create table test(id int identity(1,1) not null,name varchar(50) not null)");
            Assert.IsTrue(db.IsTableExist("test"));

            var res = await db.ExecuteSqlAsync("insert into test(name) values('jack'),('tom');");
            Assert.IsTrue(res == 2);
            res = await db.ExecuteSqlAsync("insert into test(name) values(@name1),(@name2),(@name3);", new { name1 = "小明", name2 = "小红", name3 = "小刚" }.ToDictionary());
            Assert.IsTrue(res == 3);

            res = await db.ExecuteSqlAsync("insert into test(name) values(@name1),(@name2),(@name3);", new DbParameter[]{
                db.CreatePara("name1", "小明"),
                db.CreatePara("name2", "小红"),
                db.CreatePara("name3", "小刚")
            });
            Assert.IsTrue(res == 3);
        }

        [Test]
        public async Task ExecuteSqlTest2()
        {
            //准备表
            db.Manage.DropTableIfExist("test");
            db.ExecuteSql("create table test(id int identity(1,1) not null,name varchar(50) not null)");
            Assert.IsTrue(db.IsTableExist("test"));

            db.Manage.DropProcedureIfExist("usp_test");
            db.ExecuteSql(@"
-- 使用存储过程，根据用户id获取姓名、年龄信息，并通过输出参数返回密码
create proc usp_test
@userid int =1,
@pwd varchar(50) output
as
set nocount on;
begin
	declare @age int=18
    select @userid as id,@age as age,'小明' as name
	set @pwd=CONVERT(varchar,@userid)+'_'+'123456'
end");

            //
            var pwd = db.CreatePara("pwd", null, ParameterDirection.Output, size: 50);
            var res = await db.ExecuteSqlAsync("exec usp_test @userid=1,@pwd=@pwd output", new DbParameter[] { pwd });
            pwd.Value.ShouldBe("1_123456");

            var userid = db.CreatePara("userid", 1);
            res = await db.ExecuteSqlAsync("usp_test", CommandType.StoredProcedure, 30, new DbParameter[] { userid, pwd });
            pwd.Value.ShouldBe("1_123456");
        }
    }
}
